#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库管理器
负责数据库连接、查询和事务管理
"""

import mysql.connector
from mysql.connector import pooling, Error
from typing import List, Dict, Any, Optional, Tuple
import logging
from contextlib import contextmanager
from .database_config import DATABASE_CONFIG, CONNECTION_POOL_CONFIG

class DatabaseManager:
    """
    数据库管理器
    """
    
    def __init__(self):
        """
        初始化数据库管理器
        """
        self.pool = None
        self.logger = logging.getLogger(__name__)
        self._init_connection_pool()
    
    def _init_connection_pool(self):
        """
        初始化连接池
        """
        try:
            pool_config = {
                **DATABASE_CONFIG,
                **CONNECTION_POOL_CONFIG
            }
            
            self.pool = pooling.MySQLConnectionPool(**pool_config)
            self.logger.info("数据库连接池初始化成功")
            
        except Error as e:
            self.logger.error(f"数据库连接池初始化失败: {e}")
            raise
    
    @contextmanager
    def get_connection(self):
        """
        获取数据库连接（上下文管理器）
        """
        connection = None
        try:
            connection = self.pool.get_connection()
            yield connection
        except Error as e:
            if connection:
                connection.rollback()
            self.logger.error(f"数据库操作错误: {e}")
            raise
        finally:
            if connection and connection.is_connected():
                connection.close()
    
    def execute_query(self, query: str, params: Optional[Tuple] = None) -> List[Dict[str, Any]]:
        """
        执行查询语句
        
        Args:
            query: SQL查询语句
            params: 查询参数
            
        Returns:
            查询结果列表
        """
        with self.get_connection() as connection:
            cursor = connection.cursor(dictionary=True)
            cursor.execute(query, params or ())
            result = cursor.fetchall()
            cursor.close()
            return result
    
    def execute_update(self, query: str, params: Optional[Tuple] = None) -> int:
        """
        执行更新语句（INSERT, UPDATE, DELETE）
        
        Args:
            query: SQL更新语句
            params: 更新参数
            
        Returns:
            受影响的行数
        """
        with self.get_connection() as connection:
            cursor = connection.cursor()
            cursor.execute(query, params or ())
            connection.commit()
            affected_rows = cursor.rowcount
            cursor.close()
            return affected_rows
    
    def execute_insert(self, query: str, params: Optional[Tuple] = None) -> int:
        """
        执行插入语句并返回插入的ID
        
        Args:
            query: SQL插入语句
            params: 插入参数
            
        Returns:
            插入记录的ID
        """
        with self.get_connection() as connection:
            cursor = connection.cursor()
            cursor.execute(query, params or ())
            connection.commit()
            insert_id = cursor.lastrowid
            cursor.close()
            return insert_id
    
    def execute_batch(self, query: str, params_list: List[Tuple]) -> int:
        """
        批量执行语句
        
        Args:
            query: SQL语句
            params_list: 参数列表
            
        Returns:
            受影响的总行数
        """
        with self.get_connection() as connection:
            cursor = connection.cursor()
            cursor.executemany(query, params_list)
            connection.commit()
            affected_rows = cursor.rowcount
            cursor.close()
            return affected_rows
    
    def call_procedure(self, proc_name: str, params: Optional[Tuple] = None) -> List[Any]:
        """
        调用存储过程
        
        Args:
            proc_name: 存储过程名
            params: 参数
            
        Returns:
            存储过程结果
        """
        with self.get_connection() as connection:
            cursor = connection.cursor()
            cursor.callproc(proc_name, params or ())
            
            # 获取输出参数
            results = []
            for result in cursor.stored_results():
                results.extend(result.fetchall())
            
            cursor.close()
            return results
    
    def test_connection(self) -> bool:
        """
        测试数据库连接
        
        Returns:
            连接是否成功
        """
        try:
            with self.get_connection() as connection:
                cursor = connection.cursor()
                cursor.execute("SELECT 1")
                cursor.fetchone()
                cursor.close()
                return True
        except Error as e:
            self.logger.error(f"数据库连接测试失败: {e}")
            return False
    
    def get_table_info(self, table_name: str) -> List[Dict[str, Any]]:
        """
        获取表结构信息
        
        Args:
            table_name: 表名
            
        Returns:
            表结构信息
        """
        query = "DESCRIBE {}".format(table_name)
        return self.execute_query(query)
    
    def create_stored_procedures(self):
        """
        创建存储过程和触发器
        """
        try:
            with self.get_connection() as connection:
                cursor = connection.cursor()
                
                # 先删除已存在的存储过程和触发器
                try:
                    cursor.execute("DROP PROCEDURE IF EXISTS CalculateIdleRewards")
                    cursor.execute("DROP TRIGGER IF EXISTS tr_character_auto_idle")
                except Error:
                    pass  # 忽略删除错误
                
                # 创建存储过程：计算挂机收益
                procedure_sql = """
                CREATE PROCEDURE CalculateIdleRewards(
                    IN p_character_id VARCHAR(36),
                    OUT p_exp_gained BIGINT,
                    OUT p_duration_seconds INT
                )
                BEGIN
                    DECLARE v_start_time TIMESTAMP;
                    DECLARE v_location_id INT;
                    DECLARE v_base_exp_rate DECIMAL(10,2);
                    DECLARE v_character_level INT;
                    DECLARE v_base_exp_per_hour INT;
                    DECLARE v_max_idle_hours INT;
                    DECLARE v_actual_hours DECIMAL(10,2);
                    
                    -- 获取系统配置
                    SELECT CAST(config_value AS UNSIGNED) INTO v_base_exp_per_hour 
                    FROM system_config WHERE config_key = 'base_exp_per_hour';
                    
                    SELECT CAST(config_value AS UNSIGNED) INTO v_max_idle_hours 
                    FROM system_config WHERE config_key = 'max_idle_hours';
                    
                    -- 获取角色当前挂机信息
                    SELECT ir.start_time, ir.location_id, c.level
                    INTO v_start_time, v_location_id, v_character_level
                    FROM idle_records ir
                    JOIN characters c ON ir.character_id = c.id
                    WHERE ir.character_id = p_character_id AND ir.status = 'active'
                    LIMIT 1;
                    
                    IF v_start_time IS NOT NULL THEN
                        -- 计算挂机时长
                        SET p_duration_seconds = TIMESTAMPDIFF(SECOND, v_start_time, NOW());
                        
                        -- 限制最大挂机时长
                        IF p_duration_seconds > (v_max_idle_hours * 3600) THEN
                            SET p_duration_seconds = v_max_idle_hours * 3600;
                        END IF;
                        
                        SET v_actual_hours = p_duration_seconds / 3600.0;
                        
                        -- 获取地点经验倍率
                        SELECT base_exp_rate INTO v_base_exp_rate
                        FROM idle_locations WHERE id = v_location_id;
                        
                        -- 计算经验收益
                        SET p_exp_gained = FLOOR(v_base_exp_per_hour * v_base_exp_rate * v_actual_hours * (1 + v_character_level * 0.1));
                    ELSE
                        SET p_exp_gained = 0;
                        SET p_duration_seconds = 0;
                    END IF;
                END
                """
                
                cursor.execute(procedure_sql)
                self.logger.info("存储过程 CalculateIdleRewards 创建成功")
                
                # 创建触发器：角色创建时自动开始挂机
                trigger_sql = """
                CREATE TRIGGER tr_character_auto_idle
                AFTER INSERT ON characters
                FOR EACH ROW
                BEGIN
                    -- 自动在新手村开始挂机
                    INSERT INTO idle_records (character_id, location_id, start_time, status)
                    VALUES (NEW.id, 1, NOW(), 'active');
                END
                """
                
                cursor.execute(trigger_sql)
                self.logger.info("触发器 tr_character_auto_idle 创建成功")
                
                cursor.close()
                connection.commit()
                
        except Error as e:
            self.logger.error(f"创建存储过程和触发器失败: {e}")
            raise
    
    def close_pool(self):
        """
        关闭连接池
        """
        if self.pool:
            # 注意：mysql-connector-python的连接池没有直接的close方法
            # 连接会在程序结束时自动关闭
            self.logger.info("数据库连接池已关闭")

# 全局数据库管理器实例
db_manager = DatabaseManager()